﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace DAL
{
    public class BuyerRange
    {
        public static void buyerRangeInsert(int companyId, int productId,string remarks,double amount,string businessnature)
        {
            DbHelper.ExecuteNonQuery("p_BuyerRange_insert", companyId, productId, remarks,amount,businessnature);
        }

        public static void buyerRangeSelectedItemsInsert(string productIds, int companyId)
        {
            DbHelper.ExecuteNonQuery("p_BuyerRange_SelectedItems_Insert", productIds, companyId);
        }

        public static void buyerRangeInsertByRootCategory(int companyId, int rootCategory,string businessNature)
        {
            DbHelper.ExecuteNonQuery("p_BuyerRange_insert_byRootCategory", companyId, rootCategory,businessNature);
        }

        public static void buyerRangeInsertBySubCategory(int companyId, int subCategory, string businessNature)
        {
            DbHelper.ExecuteNonQuery("p_BuyerRange_insert_bySubCategory", companyId, subCategory,businessNature);
        }

        public static void insertByProduct(string company, int productId)
        {
            DbHelper.ExecuteNonQuery("p_BuyerRange_insert_byProduct", company, productId);
        }

        /// <summary>
        /// 批量添加采购范围
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool BuyerRangeBulkCopy(int companyId, DataTable dt)
        {
            bool bulkCopyAffected = false;

            string M_ConnectionString = ConfigurationManager.ConnectionStrings["peisongDb"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(M_ConnectionString))
            {
                conn.Open();

                using (SqlCommand com = new SqlCommand("p_buyerRange_BulkDel", conn))
                {
                    SqlParameter param = new SqlParameter("@buyerId", SqlDbType.Int);
                    param.Value = companyId;

                    com.CommandType = CommandType.StoredProcedure;
                    com.Parameters.Add(param);

                    int AffectedRows = com.ExecuteNonQuery();

                    if (AffectedRows >= 0)
                    {
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                        {
                            bulkCopy.DestinationTableName = "[dbo].[buyerRange]";

                            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("buyerId", "buyerId"));
                            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("productId", "productId"));

                            bulkCopy.WriteToServer(dt);
                            bulkCopyAffected = true;
                        }
                    }
                }

            }

            return bulkCopyAffected;
        }

        /// <summary>
        /// 查询企业采购范围列表
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public static DataTable listWithCompanyRange(int companyId)
        {
            return DbHelper.ExecuteDataTable("p_buyerRange_listWithCompanyRange", companyId);
        }
        public static DataTable rootCategorylistWithCompanyId(int companyId)
        {
            return DbHelper.ExecuteDataTable("p_BuyerRange_RootCategory_listWithCompanyId", companyId);
        }
        public static DataTable subCategorylistWithCompanyIdAndParentCategoryId(int companyId, int parentCategoryId)
        {
            return DbHelper.ExecuteDataTable("p_BuyerRange_SubCategory_listWithCompanyIdAndParentCategoryId", companyId, parentCategoryId);
        }
        /// <summary>
        /// 查询企业采购范围中的大类列表--喜好商品（根据供应范围）
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public static DataTable rootCategorylistWithCompanyRange(int companyId)
        {
            return DbHelper.ExecuteDataTable("p_BuyerRange_RootCategory_listWithCompanyRange", companyId);
        }
        /// <summary>
        /// 查询企业采购范围中的小类列表--喜好商品（根据供应范围）
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="parentCategoryId"></param>
        /// <returns></returns>
        public static DataTable subCategory_listWithCompanyRange(int companyId, int parentCategoryId)
        {
            return DbHelper.ExecuteDataTable("p_BuyerRange_SubCategory_listWithCompanyRange", companyId, parentCategoryId);
        }

        /// <summary>
        /// 根据类别查询企业的采购范围--喜好商品（根据供应范围）
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public static DataTable listWithCategoryAndCompany(int companyId, int categoryId)
        {
            return DbHelper.ExecuteDataTable("p_BuyerRange_listWithCategoryIdAndCompanyId", categoryId, companyId);
        }


        /// <summary>
        /// 查询企业采购范围中的大类列表--常规商品（根据供应范围外的）
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public static DataTable rootCategorylistWithCompanyNotRange(int companyId)
        {
            return DbHelper.ExecuteDataTable("p_BuyerNotRange_RootCategory_listWithCompanyRange", companyId);
        }

        /// <summary>
        /// 查询企业采购范围中的小类列表--常规商品（根据供应范围外）
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="parentCategoryId"></param>
        /// <returns></returns>
        public static DataTable subCategory_listWithCompanyNotRange(int companyId, int parentCategoryId)
        {
            return DbHelper.ExecuteDataTable("p_BuyerNotRange_SubCategory_listWithCompamyRange", companyId, parentCategoryId);
        }
       

        /// <summary>
        /// 根据类别查询企业的采购范围--常用规商品
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public static DataTable listWithCategoryAndCompanyNotRange(int companyId, int categoryId)
        {
            return DbHelper.ExecuteDataTable("p_BuyerNotRange_listWithCategoryIdAndCompanyId", companyId, categoryId);
        }



        /// <summary>
        /// 查询大类列表--特价商品
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public static DataTable ProductSpecials_RootCategory_listCategory(int companyId)
        {
            return DbHelper.ExecuteDataTable("p_ProductSpecials_RootCategory_listCategory", companyId);
        }

        /// <summary>
        /// 小类-特价商品
        /// </summary>
        /// <param name="parentCategoryId"></param>
        /// <returns></returns>
        public static DataTable ProductSpecials_SubCategory_listCategory(int companyId,int parentCategoryId)
        {
            return DbHelper.ExecuteDataTable("p_ProductSpecials_SubCategory_listCategory", companyId,parentCategoryId);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public static DataTable ProductSpecials_listWithCategoryIdAndCompanyId(int companyId, int categoryId)
        {
            return DbHelper.ExecuteDataTable("p_ProductSpecials_listWithCategoryIdAndCompanyId", companyId, categoryId);
        }



        public static void delete(int companyId, int productId,string businessnature)
        {
            DbHelper.ExecuteNonQuery("p_BuyerRange_Del", companyId, productId,businessnature);
        }

        public static void deleteBySelected(string productIds, int companyId)
        {
            DbHelper.ExecuteNonQuery("p_BuyerRange_Del_Selected", productIds, companyId);
        }

        public static void deleteByRootCategory(int rootCategory, int companyId)
        {
            DbHelper.ExecuteNonQuery("p_BuyerRange_Del_rootCategory", rootCategory, companyId);
        }

        public static void deleteBySubCategory(int subCategory, int companyId)
        {
            DbHelper.ExecuteNonQuery("p_BuyerRange_Del_subCategory", subCategory, companyId);
        }

        /// <summary>
        /// 查询企业未采购的商品
        /// </summary>
        /// <param name="companyId">企业编号</param>
        /// <param name="where">查询筛选条件</param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public static DataSet listWithCompanySubtraction(int companyId, string where, int pageSize, int pageIndex)
        {
            SqlParameter[] param = new SqlParameter[4];

            param[0] = new SqlParameter("@companyId", SqlDbType.Int);
            param[0].Value = companyId;
            param[1] = new SqlParameter("@where", SqlDbType.NVarChar);
            param[1].Value = where;
            param[2] = new SqlParameter("@pageSize", SqlDbType.Int);
            param[2].Value = pageSize;
            param[3] = new SqlParameter("@pageIndex", SqlDbType.Int);
            param[3].Value = pageIndex;

            return DbHelper.ExecuteReDataSet("p_BuyerRange_listwithBusinessNature", param);
        }

        public static DataTable rootCategory_buyerRange_Subtraction(int companyId)
        {
            return DbHelper.ExecuteDataTable("p_buyerRange_RootCategory_Subtraction", companyId);
        }
        public static DataTable subCategory_buyerRange_Subtraction(int companyId, int parentCategoryId)
        {
            return DbHelper.ExecuteDataTable("p_buyerRange_SubCategory_Subtraction", companyId, parentCategoryId);
        }

        public static void buyerRangeInsertBySecondLevel(int companyId, int categoryId,int flag)
        {
            DbHelper.ExecuteNonQuery("p_BuyerRange_insert_bySecondLevel", companyId, categoryId,flag);
        }

        public static void buyerRangeUpdateByRemark(int companyId, int productId, string remarks,double amount,string businessnature)
        {
            DbHelper.ExecuteNonQuery("p_BuyerRange_remarksupdate",companyId,productId,remarks,amount,businessnature);
        }

        public static void buyerRangeBycompanyIdBusinessNature(int companyId, string newnature, string oldnature)
        {
            DbHelper.ExecuteNonQuery("[p_buyerRange_BycompanyIdBusinessNature]",companyId,newnature,oldnature);
        }
    }
}
